{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH_TO_DATA = 'c:/MaxTmp/Power2TheWiki/data/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1) convert sql dump into csv file  \n",
    "see https://stackoverflow.com/questions/27584405/how-to-import-a-mysqldump-into-pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "from io import StringIO\n",
    "import re, shutil\n",
    "import os.path\n",
    "\n",
    "reg1 = re.compile('`([\\w_]+)`')\n",
    "reg2 = re.compile(r'\\((?P<val>\\d+?,\\'[a-z]*?\\',\\'.*?(?:(?:\\\\\\\\)|(?<!\\\\))\\')\\)')\n",
    "\n",
    "FN_UK_LANGLINKS = 'ukwiki-20180620-langlinks.sql'\n",
    "dump_filename = PATH_TO_DATA+FN_UK_LANGLINKS\n",
    "target_table = \"langlinks\"\n",
    "\n",
    "sio = StringIO()\n",
    "\n",
    "read_mode = 0 # 0 - skip, 1 - header, 2 - data\n",
    "with open(dump_filename, 'r', encoding='ISO-8859-1') as f:\n",
    "    for line in f:\n",
    "        line = line.strip()\n",
    "        if line.lower().startswith('insert') and target_table in line:\n",
    "            read_mode = 2\n",
    "        if line.lower().startswith('create table') and target_table in line:\n",
    "            read_mode = 1\n",
    "            continue\n",
    "\n",
    "        if read_mode==0:\n",
    "            continue\n",
    "\n",
    "        # Filling up the headers\n",
    "        elif read_mode==1:\n",
    "            if line.lower().startswith('primary'):\n",
    "                # add more conditions here for different cases \n",
    "                #(e.g. when simply a key is defined, or no key is defined)\n",
    "                read_mode=0\n",
    "                sio.seek(sio.tell()-1) # delete last comma\n",
    "                sio.write('\\n')\n",
    "                continue\n",
    "            colheader = re.findall(reg1,line)\n",
    "            for col in colheader:\n",
    "                sio.write(col.strip())\n",
    "                sio.write(',')\n",
    "\n",
    "        # Filling up the data -same as @firelynx's code\n",
    "        elif read_mode ==2:\n",
    "            for match in reg2.finditer(line): \n",
    "                newline = match.group(\"val\")\n",
    "                #print(newline)\n",
    "                newline = newline.strip(' ()')\n",
    "                #print(newline)\n",
    "                newline = newline.replace('`', '')\n",
    "                #print(newline)\n",
    "                sio.write(newline)\n",
    "                sio.write(\"\\n\")\n",
    "            #if line.endswith(';'):\n",
    "                #print(\"END OF INSERT\")\n",
    "sio.seek(0)\n",
    "with open (PATH_TO_DATA+'ukwiki-20180620-langlinks.csv', 'w', encoding='ISO-8859-1') as fd:\n",
    "    shutil.copyfileobj(sio, fd,-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ll_from</th>\n",
       "      <th>ll_lang</th>\n",
       "      <th>ll_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25610</td>\n",
       "      <td>aa</td>\n",
       "      <td>Category:User de</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25692</td>\n",
       "      <td>aa</td>\n",
       "      <td>Category:User de-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>159531</td>\n",
       "      <td>aa</td>\n",
       "      <td>Template:Babel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>269084</td>\n",
       "      <td>aa</td>\n",
       "      <td>Template:Cc-by-sa-2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35964</td>\n",
       "      <td>aa</td>\n",
       "      <td>Template:Delete</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1987209</td>\n",
       "      <td>aa</td>\n",
       "      <td>Template:FlowMention</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>244518</td>\n",
       "      <td>aa</td>\n",
       "      <td>Template:Style/IPA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>244517</td>\n",
       "      <td>aa</td>\n",
       "      <td>Template:Style/yid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>25620</td>\n",
       "      <td>aa</td>\n",
       "      <td>Template:User en-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>25723</td>\n",
       "      <td>aa</td>\n",
       "      <td>Template:User fr-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>253702</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:Alexbot</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>346303</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:Beetstra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1210141</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:Ha98574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>46727</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:HenryLi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1129040</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:Hhhggg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>275511</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:Junafani</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1123004</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:Marcin Åukasz Kiejzik</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1210177</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:Minsbot</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>195083</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:PipepBot</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>159188</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:Purodha</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>50890</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:Thijs!bot</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>239400</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:TottyBot</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>193823</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:Tximist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>48131</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:Xandi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>310365</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:ÐÐºÐÐ»ÐµÐ²Ð°Ð¿</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>410329</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:ãã¼ããº</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>369810</td>\n",
       "      <td>aa</td>\n",
       "      <td>User:å°ç­æµ·é£</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>9701</td>\n",
       "      <td>aa</td>\n",
       "      <td>Wikipedia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1506746</td>\n",
       "      <td>aa</td>\n",
       "      <td>Wikipedia:Administrators</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>25609</td>\n",
       "      <td>aa</td>\n",
       "      <td>Wikipedia:Babel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804230</th>\n",
       "      <td>1506746</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:Administrators</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804231</th>\n",
       "      <td>2285790</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:Asian Month</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804232</th>\n",
       "      <td>25609</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:Babel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804233</th>\n",
       "      <td>17265</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:General disclaimer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804234</th>\n",
       "      <td>252552</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:Izehlakalo ezimanje</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804235</th>\n",
       "      <td>14035</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:Requests for adminship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804236</th>\n",
       "      <td>338928</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:Sandbox</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804237</th>\n",
       "      <td>598584</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:Statistics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804238</th>\n",
       "      <td>1209682</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:Ukuhunyushwa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804239</th>\n",
       "      <td>24692</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:Umnyango wamgwamanda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804240</th>\n",
       "      <td>1635269</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:Username policy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804241</th>\n",
       "      <td>1565735</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:VisualEditor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804242</th>\n",
       "      <td>1980584</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:Vital 100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804243</th>\n",
       "      <td>13838</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wikipedia:Vital articles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804244</th>\n",
       "      <td>27578</td>\n",
       "      <td>zu</td>\n",
       "      <td>Winston Churchill</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804245</th>\n",
       "      <td>19492</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wolfgang Amadeus Mozart</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804246</th>\n",
       "      <td>6322</td>\n",
       "      <td>zu</td>\n",
       "      <td>Wyoming</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804247</th>\n",
       "      <td>1892922</td>\n",
       "      <td>zu</td>\n",
       "      <td>XVideos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804248</th>\n",
       "      <td>1662819</td>\n",
       "      <td>zu</td>\n",
       "      <td>Yasuhiro Nakasone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804249</th>\n",
       "      <td>211214</td>\n",
       "      <td>zu</td>\n",
       "      <td>Yasuo Fukuda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804250</th>\n",
       "      <td>1067295</td>\n",
       "      <td>zu</td>\n",
       "      <td>Yoshihiko Noda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804251</th>\n",
       "      <td>618710</td>\n",
       "      <td>zu</td>\n",
       "      <td>YoshirÅ Mori</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804252</th>\n",
       "      <td>520116</td>\n",
       "      <td>zu</td>\n",
       "      <td>Yukio Hatoyama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804253</th>\n",
       "      <td>2092271</td>\n",
       "      <td>zu</td>\n",
       "      <td>Yukreyinan National Inyunyana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804254</th>\n",
       "      <td>898470</td>\n",
       "      <td>zu</td>\n",
       "      <td>Yun Bo-seon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804255</th>\n",
       "      <td>2270385</td>\n",
       "      <td>zu</td>\n",
       "      <td>Zululand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804256</th>\n",
       "      <td>311307</td>\n",
       "      <td>zu</td>\n",
       "      <td>user:Law soma</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804257</th>\n",
       "      <td>741380</td>\n",
       "      <td>zu</td>\n",
       "      <td>user:OffsBlink</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804258</th>\n",
       "      <td>473916</td>\n",
       "      <td>zu</td>\n",
       "      <td>user:Zenman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12804259</th>\n",
       "      <td>771682</td>\n",
       "      <td>zu</td>\n",
       "      <td>ÇXam</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12804260 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ll_from ll_lang                          ll_title\n",
       "0           25610      aa                  Category:User de\n",
       "1           25692      aa                Category:User de-1\n",
       "2          159531      aa                    Template:Babel\n",
       "3          269084      aa             Template:Cc-by-sa-2.0\n",
       "4           35964      aa                   Template:Delete\n",
       "5         1987209      aa              Template:FlowMention\n",
       "6          244518      aa                Template:Style/IPA\n",
       "7          244517      aa                Template:Style/yid\n",
       "8           25620      aa                Template:User en-3\n",
       "9           25723      aa                Template:User fr-1\n",
       "10         253702      aa                      User:Alexbot\n",
       "11         346303      aa                     User:Beetstra\n",
       "12        1210141      aa                      User:Ha98574\n",
       "13          46727      aa                      User:HenryLi\n",
       "14        1129040      aa                       User:Hhhggg\n",
       "15         275511      aa                     User:Junafani\n",
       "16        1123004      aa       User:Marcin Åukasz Kiejzik\n",
       "17        1210177      aa                      User:Minsbot\n",
       "18         195083      aa                     User:PipepBot\n",
       "19         159188      aa                      User:Purodha\n",
       "20          50890      aa                    User:Thijs!bot\n",
       "21         239400      aa                     User:TottyBot\n",
       "22         193823      aa                      User:Tximist\n",
       "23          48131      aa                        User:Xandi\n",
       "24         310365      aa             User:ÐÐºÐÐ»ÐµÐ²Ð°Ð¿\n",
       "25         410329      aa                 User:ãã¼ããº\n",
       "26         369810      aa                 User:å°ç­æµ·é£\n",
       "27           9701      aa                         Wikipedia\n",
       "28        1506746      aa          Wikipedia:Administrators\n",
       "29          25609      aa                   Wikipedia:Babel\n",
       "...           ...     ...                               ...\n",
       "12804230  1506746      zu          Wikipedia:Administrators\n",
       "12804231  2285790      zu             Wikipedia:Asian Month\n",
       "12804232    25609      zu                   Wikipedia:Babel\n",
       "12804233    17265      zu      Wikipedia:General disclaimer\n",
       "12804234   252552      zu     Wikipedia:Izehlakalo ezimanje\n",
       "12804235    14035      zu  Wikipedia:Requests for adminship\n",
       "12804236   338928      zu                 Wikipedia:Sandbox\n",
       "12804237   598584      zu              Wikipedia:Statistics\n",
       "12804238  1209682      zu            Wikipedia:Ukuhunyushwa\n",
       "12804239    24692      zu    Wikipedia:Umnyango wamgwamanda\n",
       "12804240  1635269      zu         Wikipedia:Username policy\n",
       "12804241  1565735      zu            Wikipedia:VisualEditor\n",
       "12804242  1980584      zu               Wikipedia:Vital 100\n",
       "12804243    13838      zu          Wikipedia:Vital articles\n",
       "12804244    27578      zu                 Winston Churchill\n",
       "12804245    19492      zu           Wolfgang Amadeus Mozart\n",
       "12804246     6322      zu                           Wyoming\n",
       "12804247  1892922      zu                           XVideos\n",
       "12804248  1662819      zu                 Yasuhiro Nakasone\n",
       "12804249   211214      zu                      Yasuo Fukuda\n",
       "12804250  1067295      zu                    Yoshihiko Noda\n",
       "12804251   618710      zu                     YoshirÅ Mori\n",
       "12804252   520116      zu                    Yukio Hatoyama\n",
       "12804253  2092271      zu     Yukreyinan National Inyunyana\n",
       "12804254   898470      zu                       Yun Bo-seon\n",
       "12804255  2270385      zu                          Zululand\n",
       "12804256   311307      zu                     user:Law soma\n",
       "12804257   741380      zu                    user:OffsBlink\n",
       "12804258   473916      zu                       user:Zenman\n",
       "12804259   771682      zu                             ÇXam\n",
       "\n",
       "[12804260 rows x 3 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df_uk_langlinks = pd.read_csv(PATH_TO_DATA+'ukwiki-20180620-langlinks.csv', encoding='ISO-8859-1', quotechar=\"'\", escapechar =\"\\\\\")\n",
    "df_uk_langlinks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(581098, 3)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_uk_en_langlinks = df_uk_langlinks[df_uk_langlinks.ll_lang == 'en']\n",
    "df_uk_en_langlinks.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "df_uk_en_langlinks.to_csv(PATH_TO_DATA+'ukwiki-20180620-langlinks_en.csv', index = False, encoding='ISO-8859-1', quotechar=\"'\", escapechar =\"\\\\\", quoting=csv.QUOTE_NONNUMERIC)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
